﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Resources;
using System.Collections;
using System.Runtime.InteropServices;
using System.IO;
using System.Configuration;

namespace QLXMDataAccessLayer.Logic
{
    public class LogicBase : ILogicBase
    {
        #region Declartions
        private MySqlConnectionStringBuilder connBuilder = null;
        private AppSettingsReader configurationAppSettings = null;
        protected MySqlConnection cnn = null;
        protected DataTable dt = null;
        protected MySqlDataAdapter adapter = null;
        protected MySqlCommand cmd = null;
        #endregion Declarations

        #region ILogicBase Members
        public MySqlConnection connectDB()
        {
            try
            {           
                string connStr = ConfigurationManager.ConnectionStrings["qltcdbConnectionString"].ConnectionString;
                return new MySqlConnection(connStr);           
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {

            }
        }

        public MySqlConnection MyConnection
        {
            get { return cnn; }
        }

        public DataTable selectData(String sql)
        {
            cnn = new MySqlConnection();
            dt = new DataTable();
            try
            {
                cnn = connectDB();
                cnn.Open();
                cmd = new MySqlCommand(sql, cnn);
                cmd.CommandTimeout = 36000;
                adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd; 
                adapter.Fill(dt);

                if (cnn.State == ConnectionState.Open)
                    cnn.Close();

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                cnn.Dispose();
                dt.Dispose();
                adapter.Dispose();
            }
        }

        public DataTable selectData(String sql, List<ParamSQL> listParam)
        {
            DataType dataType = new DataType();
            cnn = new MySqlConnection();
            dt = new DataTable();
            try
            {
                DataTable allData = new DataTable();
                cnn = connectDB();
                cnn.Open();
                cmd = new MySqlCommand(sql, cnn);
                cmd.CommandTimeout = 36000;

                adapter = new MySqlDataAdapter();
                foreach (ParamSQL param in listParam)
                {
                    String name = param.Name;
                    switch (param.Type)
                    {
                        case DataType.DATE:
                            cmd.Parameters.Add(name, MySqlDbType.Date);
                            break;
                        case DataType.INT:
                            cmd.Parameters.Add(name, MySqlDbType.Int32);
                            break;
                        case DataType.DOUBLE:
                            cmd.Parameters.Add(name, MySqlDbType.Double);
                            break;
                        case DataType.STRING:
                        default:
                            cmd.Parameters.Add(name, MySqlDbType.String);
                            break;
                    }
                    cmd.Parameters[name.ToString()].Value = param.Value;
                    cmd.Parameters[name.ToString()].Direction = ParameterDirection.Input;
                }
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);

                if (cnn.State == ConnectionState.Open)
                    cnn.Close();

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                cnn.Dispose();
                dt.Dispose();
                adapter.Dispose();
            }
        }

        public bool insertData(String sql)
        {
            try
            {
                cmd = new MySqlCommand(sql);
                cmd.CommandTimeout = 36000;
                cmd.Connection = connectDB();
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
                cmd.Connection.Open();

                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }

        public bool updateData(String sql)
        {
            cmd = new MySqlCommand();
            try
            {
                cmd = new MySqlCommand(sql);
                cmd.Connection = connectDB();
                cmd.CommandTimeout = 36000;
                cmd.Connection.Open();

                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }

        public bool deleteData(String sql)
        {
            try
            {
                cmd = new MySqlCommand(sql);
                cmd.Connection = connectDB();
                cmd.CommandTimeout = 36000;
                cmd.Connection.Open();

                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //dt.Dispose();
                cmd.Dispose();
            }
        }

        //public DataTable selectStore(String storeName)
        //{
        //    cnn = new MySqlConnection();
        //    dt = new DataTable();
        //    try
        //    {
        //        DataTable allData = new DataTable();
        //        cnn = connectDB();
        //        cnn.Open();
        //        cmd = new MySqlCommand(storeName, cnn);
        //        cmd.CommandTimeout = 36000;
        //        cmd.CommandType = CommandType.StoredProcedure;

        //        adapter = new MySqlDataAdapter(cmd);
        //        adapter.Fill(dt);

        //        if (cnn.State == ConnectionState.Open)
        //            cnn.Close();

        //        return dt;
        //    }
        //    catch (MySqlException ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        cnn.Dispose();
        //        dt.Dispose();
        //        adapter.Dispose();
        //    }
        //}

        public DataTable selectStore(String storeName, List<ParamSQL> listParam)
        {
            DataType dataType = new DataType();
            //if (cnn == null)
            cnn = new MySqlConnection();
            dt = new DataTable();
            //bool isOpen = false;
            try
            {
                DataTable allData = new DataTable();
                //if (cnn.State == ConnectionState.Closed)
                //{
                cnn = connectDB();
                cnn.Open();
                //    isOpen = true;
                //}
                cmd = new MySqlCommand(storeName, cnn);
                cmd.CommandTimeout = 36000;
                cmd.CommandType = CommandType.StoredProcedure;

                adapter = new MySqlDataAdapter();
                foreach (ParamSQL param in listParam)
                {
                    String name = param.Name;
                    String value = param.Value;
                    if ("".Equals(value) && param.Type != DataType.STRING)
                    {
                        value = null;
                    }
                    switch (param.Type)
                    {
                        case DataType.DATE:
                            cmd.Parameters.Add(name, MySqlDbType.Date);
                            break;
                        case DataType.INT:
                            cmd.Parameters.Add(name, MySqlDbType.Int32);
                            break;
                        case DataType.DOUBLE:
                            cmd.Parameters.Add(name, MySqlDbType.Double);
                            break;
                        case DataType.STRING:
                        default:
                            cmd.Parameters.Add(name, MySqlDbType.String);
                            break;
                    }
                    cmd.Parameters[name.ToString()].Value = value;
                    cmd.Parameters[name.ToString()].Direction = ParameterDirection.Input;
                }
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);

                //if (isOpen)
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                //if (isOpen)
                //{
                cnn.Dispose();
                //}
                dt.Dispose();
                adapter.Dispose();
            }
        }

        public DataTable selectStore(String storeName, params ParamSQL[] listParam)
        {
            DataType dataType = new DataType();
            //if (cnn == null)
            cnn = new MySqlConnection();
            dt = new DataTable();
            //bool isOpen = false;
            try
            {
                DataTable allData = new DataTable();
                //if (cnn.State == ConnectionState.Closed)
                //{
                cnn = connectDB();
                cnn.Open();
                //    isOpen = true;
                //}
                cmd = new MySqlCommand(storeName, cnn);
                cmd.CommandTimeout = 36000;
                cmd.CommandType = CommandType.StoredProcedure;

                adapter = new MySqlDataAdapter();
                foreach (ParamSQL param in listParam)
                {
                    String name = param.Name;
                    String value = param.Value;
                    if ("".Equals(value) && param.Type != DataType.STRING)
                    {
                        value = null;
                    }
                    switch (param.Type)
                    {
                        case DataType.DATE:
                            cmd.Parameters.Add(name, MySqlDbType.Date);
                            break;
                        case DataType.INT:
                            cmd.Parameters.Add(name, MySqlDbType.Int32);
                            break;
                        case DataType.DOUBLE:
                            cmd.Parameters.Add(name, MySqlDbType.Double);
                            break;
                        case DataType.STRING:
                        default:
                            cmd.Parameters.Add(name, MySqlDbType.String);
                            break;
                    }
                    cmd.Parameters[name.ToString()].Value = value;
                    cmd.Parameters[name.ToString()].Direction = ParameterDirection.Input;
                }
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);

                //if (isOpen)
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                //if (isOpen)
                //{
                cnn.Dispose();
                //}
                dt.Dispose();
                adapter.Dispose();
            }
        }
      
        /// <summary>
        /// Add a parameter to the command parameter array.
        /// This adds an IN parameter. The value must not be null.
        /// </summary>
        /// <param name="name">The parameter name such as "?ID"</param>
        /// <param name="val">The value to set can be any data type</param>
        public void Add(string name, Object val)
        {
            cmd.Parameters.AddWithValue(name, val);
            cmd.Parameters[name].Direction = ParameterDirection.Input;
        }
        /// <summary>
        /// Add an OUT parameter to the command parameter array.
        /// The value passed in can be null but it MUST be of the same data type
        /// as the expected output. Use the name of the field that will be
        /// assigned after the procedure executes.
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <param name="typ">The database data type</param>
        public void AddOut(string name, MySqlDbType typ)
        {
            //Type ty = val.GetType();
            cmd.Parameters.Add(new MySqlParameter(name, typ));
            cmd.Parameters[name].Direction = ParameterDirection.Output;
        }
        /// <summary>
        /// Add an INOUT parameter. The value must not be null.
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <param name="val">The value to be sent and the field to be returned</param>
        public void AddInOut(string name, Object val)
        {
            cmd.Parameters.AddWithValue(name, val);
            cmd.Parameters[name].Direction = ParameterDirection.InputOutput;
        }
        #endregion
    }
}
